package cn.zaichi.edu.excelcommon.utils;

import java.math.BigDecimal;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 获取导入excel的单元格值
 */
public class ImportCellValue {

    /**
     * 获取单元格内容值 并且转换成String
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String value = null;
        if (cell == null) {
            return "";
        } else {
            switch(cell.getCellType()) {
                case STRING:
                    value = cell.getStringCellValue();
                    break;
                case _NONE:
                    value="";
                case BLANK:
                    value = "";
                    break;
                case BOOLEAN:
                    value = cell.getBooleanCellValue() + "";
                    break;
                case FORMULA:
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    value = bd.toPlainString();
                    break;
                case NUMERIC:
                    //数值类型又具体区分日期类型，单独处理
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return DateUtil.getLocalDateTime(cell.getNumericCellValue()).format(DateTimeFormatter.ofPattern(DateFormatUtil.DATE_TIME_PATTERN));
                    }
                    BigDecimal bd1 = new BigDecimal(cell.getNumericCellValue());
                    value = bd1.toPlainString();
                    break;
                default:
                    break;
            }

            return value;
        }
    }


    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    public static String getCellValue(List<CellRangeAddress> combineCell,Sheet sheet,int row , int column){
        if(combineCell!=null&&combineCell.size()>0){
            for (CellRangeAddress ca : combineCell) {
                int firstColumn = ca.getFirstColumn();
                int lastColumn = ca.getLastColumn();
                int firstRow = ca.getFirstRow();
                int lastRow = ca.getLastRow();
                //合并单元格包括当前单元格 行 列
                if(row >= firstRow && row <= lastRow
                        &&column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        //没有合并单元格直接返回单元格内容
        Row fRow = sheet.getRow(row);
        Cell fCell = fRow.getCell(column);
        return getCellValue(fCell) ;
    }

    /**
     * 判断单元格是否是合并单元格
     * @param combineCell 所有合并集合
     * @param sheet 当前sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    public static boolean isRegion(List<CellRangeAddress> combineCell,Sheet sheet,int row , int column){
        if(combineCell!=null&&combineCell.size()>0){
            for (CellRangeAddress ca : combineCell) {
                int firstColumn = ca.getFirstColumn();
                int lastColumn = ca.getLastColumn();
                int firstRow = ca.getFirstRow();
                int lastRow = ca.getLastRow();
                //合并单元格包括当前单元格 行 列
                if(row >= firstRow && row <= lastRow
                        &&column >= firstColumn && column <= lastColumn){
                   return true;
                }
            }
        }
        return false;
    }


    /**
     * 获取所有合并单元格信息
     *
     * @param sheet
     * @return List<CellRangeAddress>
     */
    public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<>();
        //获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        //遍历所有的合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            //获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }


}
